using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Microsoft.ApplicationBlocks.Data;
using System.Collections;
using Microsoft.AceEngineering.SPM.SPMCustomDataEntity;

namespace Microsoft.AceEngineering.SPM.SPMDataLayer
{
    public class ScoreRangeDL
    {
        public static List<ScoreCDE> GetScoreData(string sortExp, int startIndex, int noOfrows)
        {
            SqlDataReader sqlreader = null;
            List<ScoreCDE> scoreRanges = null;
            ScoreCDE scoreInstance = null;

            try
            {
                string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

                SqlParameter[] myParams = new SqlParameter[3];
                myParams[0] = new SqlParameter("@sortExp", SqlDbType.VarChar);
                myParams[0].Value = sortExp;

                myParams[1] = new SqlParameter("@startRowIndex", SqlDbType.Int);
                myParams[1].Value = startIndex;

                myParams[2] = new SqlParameter("@maximumRows", SqlDbType.Int);
                myParams[2].Value = noOfrows;

                sqlreader = SqlHelper.ExecuteReader(conString, CommandType.StoredProcedure, "SPM_spGetScoreRanges", myParams);
                scoreRanges = new List<ScoreCDE>();
                while (sqlreader.Read())
                {
                    scoreInstance = new ScoreCDE();
                    scoreInstance.ScoreName = (string)sqlreader.GetValue(0);
                    scoreInstance.ScoreRangeValue = (string)sqlreader.GetValue(2);
                    scoreInstance.ScoreID = (int)sqlreader.GetValue(3);
                    scoreRanges.Add(scoreInstance);
                    scoreInstance = null;
                }
            }
            finally
            {
                if (sqlreader != null)
                {
                    if (!sqlreader.IsClosed)
                        sqlreader.Close();
                }
            }
            return scoreRanges;
        }

        public static int GetScoreCountData()
        {
            string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

            return (int)SqlHelper.ExecuteScalar(conString, CommandType.StoredProcedure, "SPM_spGetScoreCount");
        }

        public static void AddNewScoreRange(string scoreName, int thValue, string createdBy)
        {
            string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

            SqlParameter[] myParams = new SqlParameter[3];
            myParams[0] = new SqlParameter("@scoreName", SqlDbType.VarChar);
            myParams[0].Value = scoreName;

            myParams[1] = new SqlParameter("@thValue", SqlDbType.Int);
            myParams[1].Value = thValue;

            myParams[2] = new SqlParameter("@createdBy", SqlDbType.VarChar);
            myParams[2].Value = createdBy;

            SqlHelper.ExecuteNonQuery(conString, CommandType.StoredProcedure, "SPM_spInsertScoreRange", myParams);
        }

        public static void UpdateScoreRange(string scoreName, int scoreID, string createdBy)
        {
            string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

            SqlParameter[] myParams = new SqlParameter[3];
            myParams[0] = new SqlParameter("@scoreName", SqlDbType.VarChar);
            myParams[0].Value = scoreName;

            myParams[1] = new SqlParameter("@scoreID", SqlDbType.Int);
            myParams[1].Value = scoreID;

            myParams[2] = new SqlParameter("@createdBy", SqlDbType.VarChar);
            myParams[2].Value = createdBy;

            SqlHelper.ExecuteNonQuery(conString, CommandType.StoredProcedure, "SPM_spUpdateScoreRange", myParams);
        }

        public static void DeleteScoreRange(string scoreID)
        {
            string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

            SqlParameter[] myParams = new SqlParameter[1];
            myParams[0] = new SqlParameter("@scoreRangeID", SqlDbType.VarChar);
            myParams[0].Value = scoreID;

            SqlHelper.ExecuteNonQuery(conString, CommandType.StoredProcedure, "SPM_spDeleteScoreRanges", myParams);
        }

        public static int[] GetQuestionMaxAnswerWts()
        {
            int[] count = new int[2];
            SqlDataReader sqlreader = null;
            try
            {
                string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

                sqlreader = SqlHelper.ExecuteReader(conString, CommandType.StoredProcedure, "SPM_spGetQuestionMaxAnswerWt");
                sqlreader.Read();
                count[0] = Int32.Parse(sqlreader["MaxTotal"].ToString());
                count[1] = Int32.Parse(sqlreader["MaxThreshold"].ToString());
            }
            finally
            {
                if (sqlreader != null)
                {
                    if (!sqlreader.IsClosed)
                        sqlreader.Close();
                }
            }
            return count;
        }
    }
}
